/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.informet.ipd.modelo.dao;

import com.informet.ipd.modelo.vo.ActMetDepVO;
import com.informet.ipd.modelo.vo.MActividadVO;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author LAPTOP01
 */
public class MActividadDao extends DBase{


    Connection conn = null;

    public MActividadDao() {
        super();
    }

    private void setMActividad(MActividadVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_IDOBJ(rs.getInt("NUM_IDOBJ"));
        bean.setNUM_IDOBJESP(rs.getInt("NUM_IDOBJESP"));
        bean.setNUM_IDACTPER(rs.getInt("NUM_IDACTPERM"));
        bean.setNUM_IDMACTPER(rs.getInt("NUM_IDMACTPERM"));
        bean.setNUM_CODMETAAPER_PK(rs.getInt("NUM_CODMETAAPERM_PK"));
        bean.setTX_UMACTPER(rs.getString("TX_UMACTPERM"));
        bean.setNUM_CANTACTPER(rs.getInt("NUM_CANTACTPERM"));
        bean.setTX_TIPOACTPER(rs.getString("TX_TIPOACTPERM"));
        bean.setNUM_CODACTPERM_PK(rs.getInt("NUM_CODACTPERM_PK"));
    }
     private void setActMetDep(ActMetDepVO bean, ResultSet rs) throws SQLException {
     bean.setTX_DESCACTPER(rs.getString("TX_DESCACTPER"));
      bean.setTX_UMACTPER(rs.getString("TX_UMACTPER"));
      bean.setNUM_CANTACTPER(rs.getInt("NUM_CANTACTPER"));
      bean.setTX_TIPOACTPER(rs.getString("TX_TIPOACTPER"));
      bean.setNUM_PRESUACTPER(rs.getInt("NUM_PRESUACTPER"));
      bean.setTX_DESCOBJ(rs.getString("TX_DESCOBJ"));
      bean.setTX_DESCOBJESP(rs.getString("TX_DESCOBJESP"));
    }



     private void setmaximo(MActividadVO bean, ResultSet rs) throws SQLException {
        bean.setMaximo(rs.getInt("maximo"));
       //bean.setTX_RESPONACTPER(rs.getString("TX_RESPONACTPER"));
        //bean.setTX_DESCACTPER(rs.getString("TX_DESCACTPER"));

    }


      private void setMActividadCombo(MActividadVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODACTPER_PK(rs.getInt("NUM_CODACTPER_PK"));
        bean.setNUM_CODMETAAPER_PK(rs.getInt("NUM_CODMETAAPER_PK"));
        bean.setTX_UMACTPER(rs.getString("TX_UMACTPER"));
        bean.setNUM_CANTACTPER(rs.getInt("NUM_CANTACTPER"));
        bean.setTX_TIPOACTPER(rs.getString("TX_TIPOACTPER"));
  }



    public String spMan(int tipo, MActividadVO bean) {
        String msj = "";
        try {




            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_METAAPER(?,?,?,?,?,?,?,?)}");
            cs.setInt(1, tipo);
            cs.setInt(2, bean.getNUM_CODMETAAPER_PK());
            cs.setString(3, bean.getTX_UMACTPER());
            cs.setInt(4, bean.getNUM_CANTACTPER());
            cs.setString(5, bean.getTX_TIPOACTPER());
            cs.setInt(6, bean.getNUM_CODACTPER_PK());
            cs.setInt(7, 0);
            cs.setInt(8, bean.getNUM_IDMACTPER());
            cs.execute();



        } catch (Exception e) {
            msj = e.toString();
            return msj;
        }
        return msj;
    }


    public String spManM(int tipo, MActividadVO bean) {
        String msj = "";
        try {
            //JOptionPane.showMessageDialog(null, "hola spmanm");
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_METAAPERM(?,?,?,?,?,?,?,?)}");
            cs.setInt(1, tipo);
            cs.setInt(2, bean.getNUM_CODMETAAPER_PK());
            cs.setString(3, bean.getTX_UMACTPER());
            cs.setDouble(4, bean.getNUM_CANTACTPER());
            cs.setString(5, bean.getTX_TIPOACTPER());
            cs.setInt(6, bean.getNUM_CODACTPER_PK());
            cs.setInt(7, bean.getNUM_CODACTPERM_PK());
            cs.setInt(8, bean.getNUM_CODMETAAPER_PK());
            cs.execute();



        } catch (Exception e) {
            msj = e.toString();
            return msj;
        }
        return msj;
    }

    public ArrayList<MActividadVO> selectLista() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MActividadVO> lista = new ArrayList<MActividadVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select NUM_CODMETAAPER_PK,TX_UMACTPER from ME_METAAPER";
            rs = stmt.executeQuery(sql);
            MActividadVO bean = null;
            while (rs.next()) {
                bean = new MActividadVO();
                setMActividad(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

    public List getlist() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MActividadVO> lista = new ArrayList<MActividadVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select NUM_CODMETAAPER_PK,TX_UMACTPER from ME_METAAPER";
            rs = stmt.executeQuery(sql);
            MActividadVO bean = null;
            while (rs.next()) {
                bean = new MActividadVO();
                setMActividad(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

    public MActividadVO selectById(int id) throws SQLException {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Connection conn = null;
        MActividadVO bean = null;
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            String sql = "select NUM_CODMETAAPER_PK,TX_UMACTPER from ME_METAAPER where NUM_CODMETAAPER_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            bean = new MActividadVO();
            if (rs.next()) {
                setMActividad(bean, rs);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }
        }
        return bean;
    }

     public List getListById(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MActividadVO> lista = new ArrayList<MActividadVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            sql = "select og.NUM_IDOBJ,oe.NUM_IDOBJESP,actpm.NUM_IDACTPERM,mactpm.NUM_CODACTPERM_PK,mactpm.NUM_CODMETAAPERM_PK,mactpm.NUM_IDMACTPERM,mactpm.TX_UMACTPERM,mactpm.NUM_CANTACTPERM,mactpm.TX_TIPOACTPERM  from ME_OBJESP oe inner join ME_OBJ og on og.NUM_CODOBJ_PK=oe.NUM_CODOBJ_PK inner join ME_ACTPERM actpm on  actpm.NUM_CODOBJESP_PK=oe.NUM_CODOBJESP_PK  inner join ME_METAAPERM mactpm on actpm.NUM_CODACTPERM_PK=mactpm.NUM_CODACTPERM_PK inner join ME_DEPENDENCIA dep on actpm.NUM_CODDEPEN_PK=dep.NUM_CODDEPEN_PK where mactpm.NUM_CODACTPERM_PK=? order by NUM_IDMACTPERM";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            MActividadVO bean = null;
            while (rs.next()) {
                bean = new MActividadVO();
                setMActividad(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }


     public int maximo() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MActividadVO> lista = new ArrayList<MActividadVO>(0);
        String sql = "";
        int max=0;
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select max(NUM_CODMETAAPER_PK) as maximo from ME_METAAPER";
            rs = stmt.executeQuery(sql);
            MActividadVO bean = null;
            while (rs.next()) {
                bean = new MActividadVO();
                setmaximo(bean, rs);
                max=bean.getMaximo();
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return max;
    }


     public List getActMetDepById(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<ActMetDepVO> lista = new ArrayList<ActMetDepVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            sql = "select   o.TX_DESCOBJ,OE.TX_DESCOBJESP ,  ac.TX_DESCACTPER,mac.TX_UMACTPER,mac.NUM_CANTACTPER,mac.TX_TIPOACTPER,ac.NUM_PRESUACTPER from" +
    		" dbo.ME_ACTPER ac inner join dbo.ME_METAAPER mac" +
    		"  on ac.NUM_CODACTPER_PK=mac.NUM_CODACTPER_PK" +
    		" inner join ME_OBJESP oe" +
    		"  on  oe.NUM_CODOBJESP_PK= ac.NUM_CODOBJESP_PK" +
    		"  inner join ME_OBJ o" +
    		"  on  o.NUM_CODOBJ_PK=oe.NUM_CODOBJ_PK" +
    		"  where ac.NUM_CODDEPEN_PK=?";
            
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            ActMetDepVO bean = null;
            while (rs.next()) {
                bean = new ActMetDepVO();
                setActMetDep(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

//arreglar man este getactmetmbyid
     public List getActMetMById(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<ActMetDepVO> lista = new ArrayList<ActMetDepVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            sql = "select   o.TX_DESCOBJ,OE.TX_DESCOBJESP ,  ac.TX_DESCACTPER,mac.TX_UMACTPER,mac.NUM_CANTACTPER,mac.TX_TIPOACTPER,ac.NUM_PRESUACTPER from dbo.ME_ACTPER ac inner join dbo.ME_METAAPER mac  on ac.NUM_CODACTPER_PK=mac.NUM_CODACTPER_PK inner join ME_OBJESP oe  on  oe.NUM_CODOBJESP_PK= ac.NUM_CODOBJESP_PK  inner join ME_OBJ o  on  o.NUM_CODOBJ_PK=oe.NUM_CODOBJ_PK   where ac.NUM_CODACTPER_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            ActMetDepVO bean = null;
            while (rs.next()) {
                bean = new ActMetDepVO();
                setActMetDep(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }





     
     public List getCombo(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MActividadVO> lista = new ArrayList<MActividadVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);

            sql = "select TX_UMACTPER,NUM_CANTACTPER,TX_TIPOACTPER,NUM_CODMETAAPER_PK,NUM_CODACTPER_PK from ME_METAAPER where NUM_CODACTPER_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            MActividadVO bean = null;
            while (rs.next()) {
                bean = new MActividadVO();
                setMActividadCombo(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }



}
